const mysql = require("mysql");
const SqlBuilder = require('./SqlBuilder');
const mysqlConfig = require("../configs/mysql");
const scripts = require("../configs/scripts/msyql");

const pools = new Map();

/**
 * 获取数据库连接池。
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Pool} 数据库连接池
 */
function GetDbPool(dbIndex = 0) {
  if (pools.get(dbIndex)) {
    return pools.get(dbIndex);
  }

  let pool = mysql.createPool({
    host: mysqlConfig.dbs[dbIndex].ip,
    user: mysqlConfig.dbs[dbIndex].account,
    password: mysqlConfig.dbs[dbIndex].password,
    database: mysqlConfig.dbs[dbIndex].database,
    port: mysqlConfig.dbs[dbIndex].port,
    insecureAuth: true,
    timezone: '08:00'
  });

  pools.set(dbIndex, pool);

  return pool;
}

/**
 * 执行SQL查询。
 * @param {Connection} connection MySQL数据库连接对象
 * @param {String} sql SQL语句
 * @param {Array} values 参数数组
 * @returns {Promise} 异步对象
 */
let executeSQL = function (connection, sql, values) {
  return new Promise((resolve, reject) => {
    if (connection) {
      if (
        connection.state == "connected" ||
        connection.state == "authenticated"
      ) {

        // 自定义参数处理。
        connection.config.queryFormat = function (query, values) {
          if (!values) {
            return query;
          }

          let properties = Object.getOwnPropertyNames(values);

          return query.replace(/\@(\w+)/ig, function (txt, key) {
            let propName = properties.find(p => p.toLowerCase() == key.toLowerCase());

            if (propName) {
              return this.escape(values[propName]);
            }

            return txt;
          }.bind(this));
        };

        connection.query(sql, values, (err, results, fields) => {
          if (err) {
            reject(err);
          } else {
            resolve(results);
          }
        });
      } else {
        reject({
          sqlMessage: `数据库连接失败(当前状态：${connection.state})！`
        });
      }
    } else {
      reject({
        sqlMessage: "数据库连接不能为null！"
      });
    }
  });
};

/**
 * 数据库处理封装。
 * @param {Number} dbIndex 数据库服务器标识
 * @param {String} sql SQL语句
 * @param {any} values 参数
 * @param {Function} callback 动态参数设置回调函数
 * @returns {Promise} 异步对象
 */
let query = function (sql, values, callback, dbIndex = 0) {
  let tempSql = sql;

  if (typeof callback == 'function') {
    let builder = new SqlBuilder(values);

    callback(builder);

    let rs = builder.build();

    tempSql += rs;
  }

  let rs = {
    at: new Date(),
    success: false
  };

  return new Promise((resolve, reject) => {
    GetDbPool(dbIndex).getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        executeSQL(connection, tempSql, values).then(
          rows => {
            rs.datas = rows;
            rs.success = true;

            resolve(rs);
            connection.release();
          },
          reason => {
            rs.message = reason.sqlMessage || reason.message;

            reject(rs);
            connection.release();
          }
        );
      }
    });
  });
};

/**
 * 查询数据。
 * @param {String} sql 查询语句
 * @param {Array} values 参数值
 * @param {Function} callback 动态参数设置回调函数
 * @param {Number} dbIndex 数据库标识
 */
let searchDatas = (sql, values, callback = null, dbIndex = 0) => {
  sql = sql.replace(/select/i, 'select SQL_CALC_FOUND_ROWS ')

  let tempSql = sql;

  if (typeof callback == 'function') {
    let builder = new SqlBuilder(values);

    callback(builder);

    let rs = builder.build();

    tempSql += rs;
  }

  return new Promise((resolve, reject) => {
    GetDbPool(dbIndex).getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        let datas = {
          at: new Date(),
          success: false
        };

        let datasP = executeSQL(connection, tempSql, values);
        let totalsP = executeSQL(connection, 'SELECT FOUND_ROWS() AS totals;');

        let all = Promise.all([datasP, totalsP]);

        all.then(
          ([dataRs, totalRS]) => {
            datas.success = true;
            datas.total = totalRS[0]["totals"];

            datas.datas = dataRs;

            resolve(datas);
            connection.release();
          },
          reason => {
            datas.message = reason.sqlMessage;

            reject(datas);
            connection.release();
          }
        );
      }
    });
  });
};

let generateUUID = function () {
  var d = new Date().getTime();
  var uuid = 'xxxxxxxxxxxx4xxxyxxxxxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
    var r = (d + Math.random() * 16) % 16 | 0;
    d = Math.floor(d / 16);
    return (c == 'x' ? r : (r & 0x3 | 0x8)).toString(16);
  });
  return uuid;
};

module.exports = {
  query: query,
  queryForList: searchDatas,
  generateUUID: generateUUID
  // tables: getTables,
  // indexs: getIndexs,
  // tableDDL: getTableDDL,
  // columns: getTableColumns,
  // tableDatas: tableDatas,
  // dbs: mysqlConfig.dbs,
  // status: mysqlConfig.status
};